package com.han.hotelplat.comm.utils;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.han.hotelplat.comm.Constant;
import com.han.hotelplat.pojo.emtask.Emtask;
import com.han.hotelplat.pojo.task.Task;
/***
 * 
 * @Title:
 * @Description  excel文档读写
 * @author  李松茂
 *   2016-5-16 下午03:05:33
 */
@SuppressWarnings({"deprecation"})
public class HssfUtil {
	
	public static void saveHssfFile(List<String> sheetNames, List<String[]> columnNamess,
			List<List<Object[]>> datass,String savePath, String filename) throws IOException {
		FileOutputStream fos = new FileOutputStream(savePath + File.separator + filename);
		HSSFWorkbook wb = new HSSFWorkbook();
		for(int k=0;k<sheetNames.size();k++){
			String sheetName = sheetNames.get(k);
			String[] columnNames = columnNamess.get(k);
			List<Object[]> datas = datass.get(k);
			
			HSSFSheet sheet = wb.createSheet();
			wb.setSheetName(k, sheetName);
			HSSFRow row = sheet.createRow((short) 0);
			int i = 0;
			for (String columnName : columnNames) {
				HSSFCell cell = row.createCell((short) i++);
				cell.setCellValue(columnName);
			}
			i = 1;
			for (Object[] rows : datas) {
				row = sheet.createRow((short) i++);
				int j = 0;
				for (Object obj : rows) {
					if (obj == null) {
						HSSFCell cell = row.createCell((short) j++);
						cell.setCellValue("");
					} else if (obj instanceof Date) {
						HSSFCell cell = row.createCell((short) j++);
						HSSFCellStyle cellStyle = wb.createCellStyle();
						cellStyle.setDataFormat(HSSFDataFormat
								.getBuiltinFormat("m/d/yy h:mm"));
						cell.setCellValue((Date) obj);
						cell.setCellStyle(cellStyle);
					} else if (obj instanceof Double) {
						HSSFCell cell = row.createCell((short) j++);
						cell.setCellValue(((Double) obj).doubleValue());
					} else {
						HSSFCell cell = row.createCell((short) j++);
						cell.setCellValue(obj.toString());
					}
				}
			}
		}
		wb.write(fos);
	}

	public static void writeEmBalanceHssf(String sheetName, String[] columnNames,
			List<Object[]> datas, String filename, OutputStream out) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sheetName);
		HSSFRow row = sheet.createRow((short) 0);
		int i = 0;
		for (String columnName : columnNames) {
			HSSFCell cell = row.createCell((short) i++);
			cell.setCellValue(columnName);
		}
		i = 1;
		for (Object[] rows : datas) {
			row = sheet.createRow((short) i++);
			int j = 0;
			for (Object obj : rows) {
				if (obj == null) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue("");
				} else if (obj instanceof Date) {
					HSSFCell cell = row.createCell((short) j++);
					HSSFCellStyle cellStyle = wb.createCellStyle();
					cellStyle.setDataFormat(HSSFDataFormat
							.getBuiltinFormat("m/d/yy h:mm"));
					cell.setCellValue((Date) obj);
					cell.setCellStyle(cellStyle);
				} else if (obj instanceof Double) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Double) obj).doubleValue());
				} else if (obj instanceof Float) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Float) obj).floatValue());
				}else if (obj instanceof Integer) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Integer) obj).intValue());
				}else {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(obj.toString());
				}
			}
		}
		wb.write(out);
	}
	
	public static void writeHssf(String sheetName, String[] columnNames,
			List<Object[]> datas, String filename, OutputStream out) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sheetName);
		HSSFRow row = sheet.createRow((short) 0);
		int i = 0;
		for (String columnName : columnNames) {
			HSSFCell cell = row.createCell((short) i++);
			cell.setCellValue(columnName);
		}
		i = 1;
		HSSFCellStyle cellStyle = wb.createCellStyle();
		for (Object[] rows : datas) {
			row = sheet.createRow((short) i++);
			int j = 0;
			for (Object obj : rows) {
				if (obj == null) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue("");
				} else if (obj instanceof Date) {
					HSSFCell cell = row.createCell((short) j++);
					cellStyle.setDataFormat(HSSFDataFormat
							.getBuiltinFormat("m/d/yy h:mm"));
					cell.setCellValue((Date) obj);
					cell.setCellStyle(cellStyle);
				} else if (obj instanceof Double) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Double) obj).doubleValue());
				}else if (obj instanceof Float) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Float) obj).floatValue());
				}else if (obj instanceof Integer) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Integer) obj).intValue());
				} else {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(obj.toString());
				}
			}
		}
		wb.write(out);
	}
	
	public static void saveHssf(String sheetName, String[] columnNames,
			List<Object[]> datas, String filename,String savePath) throws IOException {
		FileOutputStream fos = new FileOutputStream(savePath + File.separator + filename);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sheetName);
		HSSFRow row = sheet.createRow((short) 0);
		int i = 0;
		for (String columnName : columnNames) {
			HSSFCell cell = row.createCell((short) i++);
			cell.setCellValue(columnName);
		}
		i = 1;
		for (Object[] rows : datas) {
			row = sheet.createRow((short) i++);
			int j = 0;
			for (Object obj : rows) {
				if (obj == null) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue("");
				} else if (obj instanceof Date) {
					HSSFCell cell = row.createCell((short) j++);
					HSSFCellStyle cellStyle = wb.createCellStyle();
					cellStyle.setDataFormat(HSSFDataFormat
							.getBuiltinFormat("m/d/yy h:mm"));
					cell.setCellValue((Date) obj);
					cell.setCellStyle(cellStyle);
				} else if (obj instanceof Double) {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(((Double) obj).doubleValue());
				} else {
					HSSFCell cell = row.createCell((short) j++);
					cell.setCellValue(obj.toString());
				}
			}
		}
		wb.write(fos);
	}
	
	public static List<String[]> readHssf(InputStream xlsInput,int cellNum) throws IOException{
		POIFSFileSystem fs = new POIFSFileSystem(xlsInput);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		List<String[]> dataList = new ArrayList<String[]>();
		for(int i=1;;i++){
			HSSFRow row = sheet.getRow(i);
			HSSFCell cell = row.getCell((short)0);
			if(cell==null){
				break;
			}
			if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING && "end".equals(cell.getStringCellValue())){
				break;
			}
			String[] datas = new String[cellNum];
			for(short j=0;j<cellNum;j++){
				HSSFCell data = row.getCell(j);
				switch(data.getCellType()){
					case HSSFCell.CELL_TYPE_STRING : datas[j] = data.getStringCellValue();break;
					case HSSFCell.CELL_TYPE_NUMERIC : datas[j] = Double.toString(data.getNumericCellValue());break;
					default : datas[j] = "";break;
				}				
			}
			dataList.add(datas);
		}
		return dataList;
	}
	
	public static List<String[]> readHssf(InputStream xlsInput,
			String sheetname, int startrow, int cellNum) throws IOException {
		POIFSFileSystem fs = new POIFSFileSystem(xlsInput);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheet(sheetname);
		List<String[]> dataList = new ArrayList<String[]>();
		for (int i = startrow;; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row == null || row.getCell((short) 0) == null) {
				break;
			}
			String[] datas = new String[cellNum];
			for (short j = 0; j < cellNum; j++) {
				HSSFCell data = row.getCell(j);
				if (data == null) {
					datas[j] = null;
					continue;
				}
				System.out.println(data.getCellType());
				switch (data.getCellType()) {
				case HSSFCell.CELL_TYPE_STRING:
					datas[j] = data.getStringCellValue();
					break;
				default:
					datas[j] = null;
					break;
				}
			}
			dataList.add(datas);
		}
		return dataList;
	}
	
	/**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * @param xlsInput 读取数据的源Excel
     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     * @return 读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     */
	public static String[][] readHssf2(InputStream xlsInput,String sheetName, int ignoreRows) throws FileNotFoundException, IOException {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(xlsInput);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		wb.setSheetName(0, sheetName);
		HSSFCell cell = null;
		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value = new SimpleDateFormat("yyyy-MM-dd").format(date);
								} else {
									value = "";
								}
							} else {
								value = new DecimalFormat("0").format(cell.getNumericCellValue());
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							// 导入时如果为公式生成的数据则无值
							if (!cell.getStringCellValue().equals("")) {
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							break;
						case HSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y" : "N");
							break;
						default:
							value = "";
						}
					}
					if (columnIndex == 0 && value.trim().equals("")) {
						break;
					}
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}
		xlsInput.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;

	}



	/**
	* 去掉字符串右边的空格
	* @param str 要处理的字符串
	* @return 处理后的字符串
	*/

	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);
	}
	
	public static final void main(String argv[]){
		try {
			FileInputStream is = new FileInputStream("C:\\personal\\dataExample.xls");
			List<String[]> rs = HssfUtil.readHssf(is,"skill",2,26);
			for(String[] strs:rs){
				for(String str:strs){
					System.out.print(str+";");
				}
				System.out.println("");
				System.out.println((int)Double.parseDouble(strs[0]));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void responseFile(HttpServletResponse response, HttpServletRequest request, String contentType,
			String filename, ByteArrayOutputStream baos) throws IOException {
		response.setHeader("Content-Type", contentType);
		response.setHeader("Content-Disposition", "attachment;filename=" + buildAttachmentFileName(request, filename));
		response.setHeader("Content-Length", baos.size() + "");
		baos.writeTo(response.getOutputStream());
	}

	public static String buildAttachmentFileName(HttpServletRequest request, String filename)
			throws UnsupportedEncodingException {
		String agent = request.getHeader("User-Agent");
		boolean isMSIE = (agent != null && agent.indexOf("MSIE") != -1);
		if (isMSIE) {
			filename = URLEncoder.encode(filename, "UTF-8");
		} else {
			filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
		}
		return filename;
	} 

	/*public static void writeEmTaskHssf(String sheetName,List<Emtask> datas, 
			String filename, OutputStream out) throws IOException {
		
	}*/
	
	public static void writeTaskHssf(String sheetName,List<Task> datas, 
			String filename, OutputStream out) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle cellstyle = wb.createCellStyle();//设置垂直居中格式
		cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
		
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, sheetName);
		HSSFRow row = sheet.createRow((short) 0);
		int rowNo = 0;
		short columnIndex = 0;
		List<String>columnNames = new ArrayList<String>();
		columnNames.add("任务号");
		columnNames.add("酒店");
		columnNames.add("联系人/手机号");
		columnNames.add("任务状态");
		columnNames.add("开始时间");
		columnNames.add("结束时间");
		columnNames.add("服务天数");
		columnNames.add("帮工人数");
		columnNames.add("酒店单价");
		columnNames.add("总房量");
		columnNames.add("酒店总支出");
		columnNames.add("帮工单价");
		columnNames.add("帮工总收入");
		columnNames.add("帮工姓名");
		columnNames.add("帮工收入");
		columnNames.add("帮工清洁房量 ");
		
		for (String columnName : columnNames) {
			HSSFCell cell = row.createCell((short) columnIndex++);
			cell.setCellValue(columnName);
		}
		
		rowNo=1;
		int firstRowNo=1;
		int lastRowNo=1;
		for (Task t : datas) {
			List<Emtask> emli = t.getEmtMap().get(t.getTaskid());
			firstRowNo = rowNo;
			row = sheet.createRow((short) rowNo++);
			System.out.println("创建行:"+rowNo+"任务id="+t.getTaskid());
			HSSFCell cell0 = row.createCell((short) 0);
			cell0.setCellStyle(cellstyle);
			cell0.setCellValue(t.getTaskid().toString());
			
			HSSFCell cell1 = row.createCell((short) 1);
			cell1.setCellStyle(cellstyle);
			cell1.setCellValue(t.getHotelname().toString());
			
			HSSFCell cell2 = row.createCell((short) 2);
			cell2.setCellStyle(cellstyle);
			cell2.setCellValue(t.getTel()== null?"":t.getTel());
			
			HSSFCell cell3 = row.createCell((short) 3);
			cell3.setCellStyle(cellstyle);
			cell3.setCellValue(Constant.stateExpMap.get(t.getState()));
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
			cellStyle.setDataFormat(HSSFDataFormat
					.getBuiltinFormat("m/d/yy"));
			HSSFCell cell4 = row.createCell((short) 4);
			cell4.setCellStyle(cellstyle);
			cell4.setCellValue(t.getStarttime());
			cell4.setCellStyle(cellStyle);
			
			HSSFCell cell5 = row.createCell((short) 5);
			cell5.setCellStyle(cellstyle);
			cell5.setCellValue(t.getEndtime());
			cell5.setCellStyle(cellStyle);
			
			HSSFCell cell6 = row.createCell((short) 6);
			cell6.setCellStyle(cellstyle);
			cell6.setCellValue(t.getDays());
			
			HSSFCell cell7 = row.createCell((short) 7);
			cell7.setCellStyle(cellstyle);
			cell7.setCellValue(t.getEmenroll());
			
			HSSFCellStyle cellStyle2 = wb.createCellStyle();
			cellStyle.setWrapText(true);
			
			HSSFCell cell8 = row.createCell((short) 8);
			cell8.setCellStyle(cellStyle2);
			cell8.setCellValue(t.getRoomprice());
			
			HSSFCell cell9 = row.createCell((short) 9);
			cell9.setCellStyle(cellstyle);
			cell9.setCellValue(t.getRoomCount());
			
			HSSFCell cell10 = row.createCell((short) 10);
			cell10.setCellStyle(cellstyle);
			cell10.setCellValue(t.getConsume());
			
			HSSFCell cell11 = row.createCell((short) 11);
			cell11.setCellStyle(cellStyle2);
			cell11.setCellValue(new HSSFRichTextString(t.getRoomEmprice()));
			
			HSSFCell cell12 = row.createCell((short) 12);
			cell12.setCellStyle(cellstyle);
			cell12.setCellValue(t.getEmEeward());
			
			Iterator<Emtask> its = emli.iterator();
			int ishead = 0;//是否为头部
			while (its.hasNext()) {
				Emtask emt =its.next();
				if(ishead == 1){//不是头部
					row = sheet.createRow((short) rowNo++);
					System.out.println("创建行:"+rowNo+"帮工名字："+emt.getRealname());
					lastRowNo++;
				}else{//是头部
					firstRowNo = rowNo;
					lastRowNo = rowNo;
					System.out.println("不用创建行:"+rowNo+"帮工名字："+emt.getRealname());
				}
				ishead = 1;
				
				
				HSSFCell cell13 = row.createCell((short) 13);
				cell13.setCellStyle(cellstyle);
				cell13.setCellValue(emt.getRealname());
				
				
				HSSFCell cell14 = row.createCell((short) 14);
				cell14.setCellStyle(cellstyle);
				cell14.setCellValue(emt.getReward());
				
				HSSFCell cell15 = row.createCell((short) 15);
				cell15.setCellStyle(cellstyle);
				cell15.setCellValue(emt.getSumroomcount());
			}
			if(lastRowNo > firstRowNo){
				for(int m=0 ; m<11;m++){
					System.out.println("合并行列:"+(firstRowNo-1)+";"+(lastRowNo-1)+";列:"+m);
					CellRangeAddress cra=new CellRangeAddress(firstRowNo-1, lastRowNo-1, m, m);
					sheet.addMergedRegion(cra); 
				}
			}
		}
		wb.write(out);
	}
}
